﻿
using System;
using System.Collections.Generic;
using System.Data;
using System.Reflection;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Options;
//using DV.Models;
using Newtonsoft.Json;
using System.Diagnostics;
using Microsoft.Extensions.Configuration;
using Microsoft.AspNetCore.Mvc;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using Microsoft.AspNetCore.Http;
using System.Text.RegularExpressions;
using System.Web;

using NPOI.XSSF.UserModel;
using System.Dynamic;
using System.Text;
using Microsoft.Extensions.Configuration.Json;
using Microsoft.AspNetCore.Mvc.ViewFeatures;


    /*----------------------------------------------------------------
    * 创建者：刘健
    * 电子邮箱：
    * 创建时间：20190615
    * 修改时间：20201209
    * 版本：V1.0.0
    * 描述：一些方法的集合
    *
    * ----------------------------------------------------------------
    * 修改人：
    * 时间：
    * 修改说明：
    *
    * 版本：V1.0.1
    *----------------------------------------------------------------*/

    public class GetSqlConnection
    {
        public static string MSSQLConnection { get; set; }
    }
  

    public class AppConfigurtaionServices
    {

        public static IConfiguration GetAppConfigurtaionServices()
        {
            //ReloadOnChange = true 当appsettings.json被修改时重新加载            
            return new ConfigurationBuilder()
             .Add(new JsonConfigurationSource { Path = "appsettings.json", ReloadOnChange = true })
             .Build();
        }
    }




public static class BaseModel<T>
{

       public static T ModelData { get; set; }

       public static string Code { get; set; }

       public static string Msg { get; set; }

    //public static BaseModel<T> ResponseData(T Model,string Codes,string Msgs)
    //{

    //    // list.Add(this.Model=Model)

    //    BaseModel.ModelData = Model;
    //       Code = Codes;
    //       Msg = Msgs;

    //    //list.Add();
    //    return BaseModel;
        
    //}
}
/// <summary>
/// NetCore使用
/// </summary>
public class MyUtils
    {
      

    #region 自己的 NetCore使用

    public static string MSSQLConnection { get; set; }

        public static SqlConnection GetConn { get; set; }
        //{
        /// <summary>
        /// 获取数据库连接
        /// </summary>
        private static IConfiguration Configuration { get; set; }

        public static SqlConnection createConn()
        {
            Configuration = AppConfigurtaionServices.GetAppConfigurtaionServices();
            return GetConn = new SqlConnection(Configuration.GetSection("ConnectionStrings")["MSSQLConnection"]);
        }
        //public static BaseModel ReturnModelData(string code, string msg, object model = null, object fieldname = null)
        //{
        //    BaseModel basemodel = new BaseModel();
        //    basemodel.code = code;
        //    basemodel.msg = msg;
        //    basemodel.model = model;
        //    return basemodel;
        //}

        //public static BaseModel<T> ReturnModelData<T>(string code, string msg, object model = null, object models = null)
        //{
        //    BaseModel<T> basemodel = new BaseModel<T>();
        //    basemodel.code = code;
        //    basemodel.msg = msg;
        //    basemodel.model = (T)model;
        //    basemodel.models= (List<T>)models;
        //    //basemodel.fieldname = fieldname;
        //    return basemodel;
        //}

        ///也可以用
        //public static SqlConnection createConn()
        //{

        //    string connStr = AppConfigurtaionServices.Configuration["ConnectionStrings:MSSQLConnection"].ToString();

        //    return conn = new SqlConnection(connStr);
        //}
        /// <summary>
        /// 将数字状态转化汉字
        /// </summary>
        /// <returns></returns>
        public static DataTable ConvertedFieldValue(DataTable dt)
        {
            if (dt != null)
            {
                if (dt.Rows.Count > 0 && dt.Columns.Count > 0)
                {
                    if (dt.Columns.Contains("WhetherToDeactive"))
                    {
                        foreach (DataRow dr in dt.Rows)
                        {
                            if (dr["WhetherToDeactive"].ToString().Equals("2"))
                            {
                                dr["WhetherToDeactive"] = "停用中";
                            }
                            else if (dr["WhetherToDeactive"].ToString().Equals("1"))
                            {
                                dr["WhetherToDeactive"] = "正常";
                            }
                            dt.Rows.Add(dr);
                        }
                    }
                    else
                    {
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            if (dt.Rows[i]["是否停用"].ToString().Equals("2"))
                            {
                                dt.Rows[i]["是否停用"] = "停用中";
                            }
                            else if (dt.Rows[i]["是否停用"].ToString().Equals("1"))
                            {
                                dt.Rows[i]["是否停用"] = "正常";
                            }
                            else
                            {
                                dt.Rows[i]["是否停用"] = "未知";
                            }
                        }
                    }
                }
            }
            return dt;
        }


        ///// <summary>
        ///// 出错日志保存
        ///// </summary>
        ///// <param name=" "></param>
        ///// <param name="Class_Name">MethodBase.GetCurrentMethod().DeclaringType.Name  当前出错类</param>
        ///// <param name="Error_Function">MethodBase.GetCurrentMethod().Name 当前出错方法</param>
        public static void Get_Try_Catch(Exception  ex, string Class_Name, string Error_Function)
        {
            string path = System.AppDomain.CurrentDomain.BaseDirectory.ToString() + "/" + MethodBase.GetCurrentMethod().DeclaringType.Namespace + ".log";
            // string path = Application.StartupPath + @"/xls_read_log.txt";//System.Windows.Forms.Application.StartupPath;
            if (!System.IO.File.Exists(path))
            {
                //没有则创建这个文件
                //  "C:\\xls_read_log.txt"
                FileStream fs1 = new FileStream(path, FileMode.Create, FileAccess.Write);//创建写入文件                //设置文件属性为隐藏
                                                                                         //  System.IO.File.SetAttributes( "@"+path + "\\xls_read_log.txt", FileAttributes.Hidden);
                StreamWriter sw = new StreamWriter(fs1);
                sw.Write("错误捕捉时间:" + DateTime.Now.ToString() + "__Class_Name:" + Class_Name + "__Error_Function:" + Error_Function + " __错误信息:" +  ex.Message + "StackTrace" +  ex.StackTrace);//开始写入值
                sw.WriteLine();
                sw.Close();
                fs1.Close();
            }
            else
            {
                StreamWriter sr = new StreamWriter(path, true);
                sr.Write("错误捕捉时间:" + DateTime.Now.ToString() + "__Class_Name:" + Class_Name + "__Error_Function:" + Error_Function + " __错误信息:" +  ex.Message + "StackTrace" +  ex.StackTrace);//开始写入值
                sr.WriteLine();
                sr.Close();
                //  fs.Close();
            }
            //public staticMethod.Kill(Check_Error.excel);
        }


        public static void Get_Try_Catch(Exception ex)
        {
            string path = System.AppDomain.CurrentDomain.BaseDirectory.ToString() + "/DV.log";
            // string path = Application.StartupPath + @"/xls_read_log.txt";//System.Windows.Forms.Application.StartupPath;
            if (!System.IO.File.Exists(path))
            {
                //没有则创建这个文件
                //  "C:\\xls_read_log.txt"
                FileStream fs1 = new FileStream(path, FileMode.Create, FileAccess.Write);//创建写入文件                //设置文件属性为隐藏
                                                                                         //  System.IO.File.SetAttributes( "@"+path + "\\xls_read_log.txt", FileAttributes.Hidden);
                StreamWriter sw = new StreamWriter(fs1);
                sw.Write("错误捕捉时间:" + DateTime.Now.ToString() + "错误信息:" +  ex.Message + "错误明细" +  ex.StackTrace);//开始写入值
                sw.WriteLine();
                sw.Close();
                fs1.Close();
            }
            else
            {
                StreamWriter sr = new StreamWriter(path, true);
                sr.Write("错误捕捉时间:" + DateTime.Now.ToString() + "错误信息:" +  ex.Message + "错误明细" +  ex.StackTrace);//开始写入值
                sr.WriteLine();
                sr.Close();
                //  fs.Close();
            }
            //public staticMethod.Kill(Check_Error.excel);
        }
        /// <summary>
        /// 给前端一些的字符对象添加[]可以转为实体对象数据
        /// </summary>
        /// <returns></returns>
        public static string AddParenthesis(string data)
        {
            return "[" + data + "]";
        }


        /// <summary>
        /// 自定义返回消息
        /// </summary>
        /// <param name="statecode">状态码，前端判断用</param>
        /// <param name="msg">返回的消息</param>
        /// <returns></returns>
        public static string ReturnJsonMsg(string statecode, string msg, object data = null)
        {
            return JsonConvert.SerializeObject(new { code = statecode, msg = msg, data = data });
        }
        /// <summary>
        /// 检查集合是否为空,(检查datatable)
        /// </summary>
        /// <returns>集合</returns>
        public static string CheckListData(List<DataTable> ds) 
        {
            int value = 0;
            foreach (var s in ds)
            {
                if (s.Rows.Count > 0)
                {
                    value++;
                }
            }
            if (value == ds.Count)
            {
                return ReturnJsonMsg("1", "success,查询成功", ds);
            }
            else if (value < ds.Count&&value>0)
            {
                return ReturnJsonMsg("1", "error,有部分数据未查询到", ds);
            }
            else 
            {
                return ReturnJsonMsg("0", "error,未查询到数据");
            }
        }

        /// <summary>
        /// 不检查集合是否为空,英语重载时使用(检查datatable)
        /// </summary>
        /// <returns>集合</returns>
        public static string UnCheckListData(List<DataTable> ds)
        {
            int value = 0;
            foreach (var s in ds)
            {
                if (s.Rows.Count > 0)
                {
                    value++;
                }
            }
            if (value == ds.Count)
            {
                return ReturnJsonMsg("1", "success,查询成功", ds);
            }
            else if (value < ds.Count && value > 0)
            {
                return ReturnJsonMsg("1", "error,有部分数据未查询到", ds);
            }
            else
            {
                return ReturnJsonMsg("1", "error,未查询到数据", ds);
            }
        }



        /// <summary>
        /// 返回值
        /// </summary>
        /// <param name="bol"></param>
        /// <returns></returns>
        public static string returndata(int bol)
        {
            string retuenval = "";
            if (bol == 1)
            {
                retuenval = JsonConvert.SerializeObject(new { code = "1", msg = "success" });
            }
            else if (bol == 0)
            {
                retuenval = JsonConvert.SerializeObject(new { code = "0", msg = "error,出错啦，请检查" });
            }
            else if (bol == 10)
            {
                retuenval = JsonConvert.SerializeObject(new { code = "0", msg = "未找到数据" });
            }
            else
            {
                retuenval = JsonConvert.SerializeObject(new { code = "0", msg = "未知问题" });
            }
            return retuenval;

        }
        /// <summary>
        /// 检测datatable是否w为null和空
        /// </summary>
        /// <returns></returns>
        public static bool CheckDataTable(DataTable dt)
        {
            if (dt == null || dt.Rows.Count == 0)
            {
                return false;
            }
            else 
            {
                return true;
            }
        
        }

        /// <summary>
        /// 返回值
        /// </summary>
        /// <param name="bol"></param>
        /// <returns></returns>
        public static string returndata(bool bol)
        {
            if (bol == true)
            {
               return JsonConvert.SerializeObject(new { code = "1", msg = "success,成功" });
            }
            else if (bol == false)
            {

                return JsonConvert.SerializeObject(new { code = "0", msg = "error,不成功，请检查数据是否正确" });
            }
            else
            {
                return JsonConvert.SerializeObject(new { code = "0", msg = "error,系统出错啦" });
            }
        }
        /// <summary>
        /// 判断datatable内是否有数据并返回json
        /// </summary>
        /// <returns></returns>
        public static string JudgeDateTable(DataTable dt)
        {
            if (dt != null)
            {
                if (dt.Rows.Count > 0 && dt.Columns.Count > 0)
                {
                    return returndata(dt, 1, dt.Rows.Count);
                }
                else if (dt.Rows.Count == 0 && dt.Columns.Count > 0)
                {
                    return returndata(dt, 10, dt.Rows.Count);
                }
                else
                {
                    return returndata(dt, 0, dt.Rows.Count);
                }
            }
            else
            {
                return returndata(dt, 10, dt.Rows.Count);
            }

        }
        /// <summary>
        /// 返回data，状态等
        /// </summary>
        /// <returns></returns>
        public static string returndata(DataTable dt, int type, int count = 0)
        {
            Dictionary<string, object> dic = new Dictionary<string, object>();

            switch (type)
            {
                case 0:
                    dic.Add("code", "0");
                    dic.Add("msg", "error，出错啦");
                    break;
                //有总数的返回
                case 1:
                    dic.Add("data", dt);
                    dic.Add("code", "1");
                    dic.Add("msg", "success");
                    dic.Add("count", count);
                    break;
                //返回dt
                case 2:
                    dic.Add("data", dt);
                    dic.Add("code", "1");
                    dic.Add("msg", "success");
                    break;
                //返回信息
                case 3:
                    dic.Add("code", "1");
                    dic.Add("msg", "success");
                    break;

                case 10:
                    dic.Add("code", "0");
                    dic.Add("msg", "error,未查询到数据");
                    break;
                case 11:
                    dic.Add("code", "0");
                    dic.Add("msg", "error,未将数据提交到后台");
                    break;
                //返回默认
                default:
                    dic.Add("code", "0");
                    dic.Add("msg", "error,出错啦");
                    break;
            }
            return JsonConvert.SerializeObject(dic);
        }
        /// <summary>
        /// 读取数据返回datatable,入参查询语句sql
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns></returns>
        public static DataTable ReadDataTable(string sql)
        {
            sql = sql.ToString().Replace("[", "").Replace("]", "");
            DataTable dt = new DataTable();
            SqlConnection Conn = createConn();
            SqlCommand cmd = new SqlCommand(sql, Conn);
            try
            {
                Conn.Open();
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                sda.Fill(ds);
                //Conn.Close();
                if (ds != null && ds.Tables.Count > 0)
                {
                    dt = ds.Tables[0];
                }
            }
            catch (Exception  e)
            {
                Console.WriteLine(e);
            }
            finally
            {
                Conn.Close();
                cmd.Dispose();
            }
            return dt;
        }
        /// <summary>
        /// 读取数据返回datatable,入参查询语句sql,  动态参数SqlParameter
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="sqlPara">SqlParameter</param>
        /// <returns></returns>
        public static DataTable ReadDataTable(string sql, SqlParameter[] sqlPara)
        {
            sql = sql.ToString().Replace("[", "").Replace("]", "");
            DataTable dt = new DataTable();
            SqlConnection Conn = createConn();
            SqlCommand cmd = new SqlCommand(sql, Conn);
            if (sqlPara != null)
                cmd.Parameters.AddRange(sqlPara);
            try
            {
                Conn.Open();
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                sda.Fill(ds);
                if (ds != null && ds.Tables.Count > 0)
                {
                    dt = ds.Tables[0];
                }
            }
            catch (Exception  ex)
            {
                Console.WriteLine(ex);
            }
            finally
            {
                cmd.Parameters.Clear();
                Conn.Close();
                cmd.Dispose();
            }
            return dt;
        }
        /// <summary>
        /// 读取数据返回datatable,入参查询语句sql,  动态参数SqlParameter
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="sqlPara">SqlParameter</param>
        /// <returns></returns>
        public static DataTable ReadDataTable(string sql, SqlParameter sqlPara)
        {
            sql = sql.ToString().Replace("[", "").Replace("]", "");
            DataTable dt = new DataTable();
            SqlConnection Conn = createConn();
            SqlCommand cmd = new SqlCommand(sql, Conn);
            if (sqlPara != null)
                cmd.Parameters.Add(sqlPara);
            try
            {
                Conn.Open();
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                sda.Fill(ds);
                if (ds != null && ds.Tables.Count > 0)
                {
                    dt = ds.Tables[0];
                }
            }
            catch (Exception  e)
            {
                Console.WriteLine(e);
            }
            finally
            {
                cmd.Parameters.Clear();
                Conn.Close();
                cmd.Dispose();
            }
            return dt;
        }

        /// <summary>
        /// 读取数据返回Dictionary,入参查询语句sql
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns></returns>
        public static List<DataTable> ReadDataSet(string sql)
        {
            sql = sql.ToString().Replace("[", "").Replace("]", "");
            List<DataTable> list = new List<DataTable>();
            SqlConnection Conn = createConn();
            SqlCommand cmd = new SqlCommand(sql, Conn);
            DataSet ds = null;
            try
            {
                Conn.Open();
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                ds = new DataSet();
                sda.Fill(ds);
                //Conn.Close();
                if (ds != null && ds.Tables.Count > 0)
                {
                    for (int i = 0; i < ds.Tables.Count; i++)
                    {
                        list.Add(ds.Tables[i]);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }
            finally
            {
                Conn.Close();
                cmd.Dispose();
            }
            return list;
        }

        /// <summary>
        /// 读取数据返回Dictionary,入参查询语句sql,  动态参数SqlParameter
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="sqlPara">SqlParameter</param>
        /// <returns></returns>
        public static List<DataTable> ReadDataSet(string sql, SqlParameter[] sqlPara)
        {
            sql = sql.ToString().Replace("[", "").Replace("]", "");
            List<DataTable> list = new List<DataTable>(); ;

            SqlConnection Conn = createConn();
            SqlCommand cmd = new SqlCommand(sql, Conn);
            DataSet ds = null;
            if (sqlPara != null)
                cmd.Parameters.AddRange(sqlPara);
            try
            {
                Conn.Open();
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                ds = new DataSet();
                sda.Fill(ds);
                if (ds != null && ds.Tables.Count > 0)
                {
                    for (int i = 0; i < ds.Tables.Count; i++)
                    {
                        list.Add(ds.Tables[i]);
                    }
                }
            }
            catch (Exception  e)
            {
                Console.WriteLine(e);
            }
            finally
            {
                Conn.Close();
                cmd.Dispose();
            }

            return list;

        }
        /// <summary>
        /// 读取数据返回Dictionary,入参查询语句sql,  动态参数SqlParameter
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="sqlPara">SqlParameter</param>
        /// <returns></returns>
        public static List<DataTable> ReadDataSet(string sql, SqlParameter sqlPara)
        {
            sql = sql.ToString().Replace("[", "").Replace("]", "");
            List<DataTable> list = new List<DataTable>(); ;

            SqlConnection Conn = createConn();
            SqlCommand cmd = new SqlCommand(sql, Conn);
            DataSet ds = null;
            if (sqlPara != null)
                cmd.Parameters.Add(sqlPara);
            try
            {
                Conn.Open();
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                ds = new DataSet();
                sda.Fill(ds);
                if (ds != null && ds.Tables.Count > 0)
                {
                    for (int i = 0; i < ds.Tables.Count; i++)
                    {
                        list.Add(ds.Tables[i]);
                    }
                }
            }
            catch (Exception  )
            {

            }
            finally
            {
                Conn.Close();
                cmd.Dispose();
            }

            return list;

        }

        #endregion

        #region 第三方

        //--------------第三方-------------------------
        /// <summary>
        /// 泛型集合转化为dataTable
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="list">集合</param>
        /// <returns></returns>
        public static DataTable ToDataTable<T>(IEnumerable<T> list)
        {

            //创建属性的集合    
            List<PropertyInfo> pList = new List<PropertyInfo>();
            //获得反射的入口    

            Type type = typeof(T);
            DataTable dt = new DataTable();
            //把所有的public属性加入到集合 并添加DataTable的列    
            Array.ForEach<PropertyInfo>(type.GetProperties(), p => { pList.Add(p); dt.Columns.Add(p.Name, p.PropertyType); });
            foreach (var item in list)
            {
                //创建一个DataRow实例    
                DataRow row = dt.NewRow();
                //给row 赋值    
                pList.ForEach(p => row[p.Name] = p.GetValue(item, null));
                //加入到DataTable    
                dt.Rows.Add(row);
            }
            return dt;
        }
        /// <summary>
        /// 去除空白行
        /// </summary>
        /// <param name="dt"></param>
        public static DataTable RemoveEmpty(DataTable dt)
        {
            List<DataRow> removelist = new List<DataRow>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                bool IsNull = true;
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (!string.IsNullOrEmpty(dt.Rows[i][j].ToString().Trim()))
                    {
                        IsNull = false;
                    }
                }
                if (IsNull)
                {
                    removelist.Add(dt.Rows[i]);
                }
            }
            for (int i = 0; i < removelist.Count; i++)
            {
                dt.Rows.Remove(removelist[i]);
            }
            return dt;
        }
        /// <summary>
        /// 实体类集合转table
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public static DataTable FillDataTable<T>(List<T> modelList)
        {
            if (modelList == null || modelList.Count == 0)
                return null;
            DataTable dt = CreatTable(modelList[0]);
            foreach (T model in modelList)
            {
                DataRow dr = dt.NewRow();
                foreach (PropertyInfo p in typeof(T).GetProperties())
                {
                    dr[p.Name] = p.GetValue(model, null);
                }
                dt.Rows.Add(dr);
            }
            return dt;
        }

        /// <summary>
        /// 根据实体创建table
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        private static DataTable CreatTable<T>(T model)
        {
            DataTable dt = new DataTable(typeof(T).Name);
            foreach (PropertyInfo p in typeof(T).GetProperties())
            {
                dt.Columns.Add(new DataColumn(p.Name, p.PropertyType));
            }
            return dt;
        }

        /// <summary>
        /// 实体类转表
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="entities"></param>
        /// <returns></returns>
        public static DataTable List2DataTable<TEntity>(List<TEntity> entities)
        {
            if (entities == null)
            {
                throw new ArgumentNullException("转换的集合为空");
            }
            Type type = typeof(TEntity);
            PropertyInfo[] properties = type.GetProperties();
            DataTable dt = new DataTable(type.Name);
            foreach (var item in properties)
            {
                dt.Columns.Add(new DataColumn(item.Name) { DataType = item.PropertyType });
            }
            foreach (var item in entities)
            {
                DataRow row = dt.NewRow();
                foreach (var property in properties)
                {
                    row[property.Name] = property.GetValue(item);
                }
                dt.Rows.Add(row);
            }
            return dt;
        }

        /// <summary>
        /// 原作者:徐晓硕
        /// 邮箱:xuxiaoshuo@fang.com
        /// 版本:v1.0.0
        /// 过滤参数的规则
        /// </summary>
        private static Regex reg = new Regex(@"@\S{1,}?(,|\s|;|--|\)|$)");

        private static char[] filterChars = new char[] { ' ', ',', ';', '-', ')' };

        /// <summary>
        /// 根据sql语句和实体对象自动生成参数化查询SqlParameter列表
        /// </summary>
        /// <typeparam name="T">实体对象类型</typeparam>
        /// <param name="sqlStr">sql语句</param>
        /// <param name="obj">实体对象</param>
        /// <returns>SqlParameter列表</returns>
        public static List<SqlParameter> ChangeSqlParameter<T>(String sqlStr, T obj)
        {
            List<SqlParameter> parameters = new List<SqlParameter>();

            List<string> listStr = new List<string>();
            System.Text.RegularExpressions.Match mymatch = reg.Match(sqlStr);
            while (mymatch.Success)
            {
                listStr.Add(mymatch.Value.TrimEnd(filterChars).TrimStart('@'));
                mymatch = mymatch.NextMatch();
            }
            Type t = typeof(T);

            PropertyInfo[] pinfo = t.GetProperties();

            foreach (var item in listStr)
            {
                for (int i = 0; i < pinfo.Length; i++)
                {
                    if (item.Equals(pinfo[i].Name, StringComparison.OrdinalIgnoreCase))
                    {
                        parameters.Add(new SqlParameter() { ParameterName = "@" + item, Value = pinfo[i].GetValue(obj, null) });
                        break;
                    }
                    else
                    {
                        if (i == pinfo.Length - 1)
                        {
                            throw new Exception("查询参数@" + item + "在类型" + t.ToString() + "中未找到赋值属性");
                        }
                    }
                }
            }

            return parameters;
        }
        public static SqlParameter[] ChangeSqlParameterToArray<T>(String sqlStr, T obj)
        {
            List<SqlParameter> parameters = new List<SqlParameter>();

            List<string> listStr = new List<string>();

            System.Text.RegularExpressions.Match mymatch = reg.Match(sqlStr);
            System.Text.RegularExpressions.MatchCollection mymatchs = reg.Matches(sqlStr);
            
            while (mymatch.Success)
            {
                if (!listStr.Contains(mymatch.Value.TrimEnd(filterChars).TrimStart('@')))
                {
                    listStr.Add(mymatch.Value.TrimEnd(filterChars).TrimStart('@'));    
                }
                mymatch = mymatch.NextMatch();
            }
            Type t = typeof(T);
            PropertyInfo[] pinfo = t.GetProperties();

            foreach (var items in listStr)
            {
                string item = items.ToString().Replace("[", "").Replace("]", "");
                for (int i = 0; i < pinfo.Length; i++)
                {
                    if (item.Equals(pinfo[i].Name, StringComparison.OrdinalIgnoreCase))
                    {
                        parameters.Add(new SqlParameter() { SourceColumn= item, ParameterName = "@" + item, Value = pinfo[i].GetValue(obj, null) });
                        break;
                    }
                    else
                    {
                        if (i == pinfo.Length - 1)
                        {
                            throw new Exception("查询参数@" + item + "在类型" + t.ToString() + "中未找到赋值属性");
                        }
                    }
                }
            }
            return parameters.ToArray();
        }
        /// <summary>
        /// 根据sql语句和实体对象自动生成参数化查询SqlParameter列表
        /// </summary>
        /// <param name="sqlStr">sql语句</param>
        /// <param name="obj">实体对象</param>
        /// <returns>SqlParameter列表</returns>
        public static List<SqlParameter> ChangeSqlParameter(String sqlStr, object obj)
        {
            List<SqlParameter> parameters = new List<SqlParameter>();

            List<string> listStr = new List<string>();
            System.Text.RegularExpressions.Match mymatch = reg.Match(sqlStr);
            while (mymatch.Success)
            {
                listStr.Add(mymatch.Value.TrimEnd(filterChars).TrimStart('@'));
                mymatch = mymatch.NextMatch();
            }
            Type t = obj.GetType();

            PropertyInfo[] pinfo = t.GetProperties();

            foreach (var item in listStr)
            {
                for (int i = 0; i < pinfo.Length; i++)
                {
                    if (item.Equals(pinfo[i].Name, StringComparison.OrdinalIgnoreCase))
                    {
                        parameters.Add(new SqlParameter() { ParameterName = "@" + item, Value = pinfo[i].GetValue(obj, null) });
                        break;
                    }
                    else
                    {
                        if (i == pinfo.Length - 1)
                        {
                            throw new Exception("查询参数@" + item + "在类型" + t.ToString() + "中未找到赋值属性");
                        }
                    }
                }
            }

            return parameters;
        }

        /// <summary>
        /// 根据sql语句和ExpandoObject对象自动生成参数化查询SqlParameter列表
        /// </summary>
        /// <param name="sqlStr">sql语句</param>
        /// <param name="obj">ExpandoObject对象</param>
        /// <returns>SqlParameter列表</returns>
        public static List<SqlParameter> ChangeSqlParameter(String sqlStr, ExpandoObject obj)
        {
            List<SqlParameter> parameters = new List<SqlParameter>();

            List<string> listStr = new List<string>();
            System.Text.RegularExpressions.Match mymatch = reg.Match(sqlStr);
            while (mymatch.Success)
            {
                listStr.Add(mymatch.Value.TrimEnd(filterChars).TrimStart('@'));
                mymatch = mymatch.NextMatch();
            }
            IDictionary<String, Object> dic = (IDictionary<String, Object>)obj;

            foreach (var item in listStr)
            {
                int reachCount = 0;
                foreach (var property in dic)
                {
                    if (item.Equals(property.Key, StringComparison.OrdinalIgnoreCase))
                    {
                        parameters.Add(new SqlParameter() { ParameterName = "@" + item, Value = property.Value });
                        break;
                    }
                    else
                    {
                        if (reachCount == dic.Count - 1)
                        {
                            throw new Exception("查询参数@" + item + "在类型ExpandoObject中未找到赋值属性");
                        }
                    }
                    reachCount++;
                }
            }
            return parameters;
        }
        #endregion
        #region 按原表导出
        /// <summary>
        /// 原表导出
        /// </summary>
        /// <param name="dt">DataTable</param>    
        /// <param name="outExcelName">输出Excel名称</param>
        /// <param name="context">HttpContext</param>
        /// <param name="boolString">是否按原有数据类型导出,0原数据类型；1全转为string</param>
        public static byte[] NopiToExcelFromDatable(DataTable dt, string outExcelName, HttpContext context, int boolString)
        {
            MemoryStream ms = new MemoryStream();

            using (dt)
            {
                IWorkbook workbook = new XSSFWorkbook();
                ISheet sheet = workbook.CreateSheet();
                IRow headerRow = sheet.CreateRow(0);

                XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle;
                XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat;
                dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

                // handling header.
                foreach (DataColumn column in dt.Columns)
                    headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);//If Caption not set, returns the ColumnName value

                // handling value.
                int rowIndex = 1;

                foreach (DataRow row in dt.Rows)
                {
                    IRow dataRow = sheet.CreateRow(rowIndex);

                    foreach (DataColumn column in dt.Columns)
                    {
                        if (boolString == 0)//按原数据类型
                        {
                            XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell;
                            string drValue = row[column].ToString();
                            switch (column.DataType.ToString())
                            {
                                case "System.String": //字符串类型
                                    double result;
                                    if (isNumeric(drValue, out result))
                                    {

                                        double.TryParse(drValue, out result);
                                        newCell.SetCellValue(result);
                                        break;
                                    }
                                    else
                                    {
                                        newCell.SetCellValue(drValue);
                                        break;
                                    }

                                case "System.DateTime": //日期类型
                                    DateTime dateV;
                                    DateTime.TryParse(drValue, out dateV);
                                    newCell.SetCellValue(dateV);

                                    newCell.CellStyle = dateStyle; //格式化显示
                                    break;
                                case "System.Boolean": //布尔型
                                    bool boolV = false;
                                    bool.TryParse(drValue, out boolV);
                                    newCell.SetCellValue(boolV);
                                    break;
                                case "System.Int16": //整型
                                case "System.Int32":
                                case "System.Int64":
                                case "System.Byte":
                                    int intV = 0;
                                    int.TryParse(drValue, out intV);
                                    newCell.SetCellValue(intV);
                                    break;
                                case "System.Decimal": //浮点型
                                case "System.Double":
                                    double doubV = 0;
                                    double.TryParse(drValue, out doubV);
                                    newCell.SetCellValue(doubV);
                                    break;
                                case "System.DBNull": //空值处理
                                    newCell.SetCellValue("");
                                    break;
                                default:
                                    newCell.SetCellValue("");
                                    break;
                            }
                        }

                        if (boolString == 1)//全转为string
                        {
                            dataRow.CreateCell(column.Ordinal).SetCellValue(string.Format("{0:N2}", row[column]).Replace(".00", ""));
                        }
                    }

                    rowIndex++;
                }
                AutoSizeColumns(sheet);

                workbook.Write(ms);


                var buffer = ms.GetBuffer();
                ms.Flush();
                ms.Close();
                //  ms.Position = 0;
                return buffer;

            }

            /*   if (context.Request..Browser == "IE")
                   outExcelName = HttpUtility.UrlEncode(outExcelName);
               context.Response.Headers.Add("Content-Disposition", "attachment;fileName=" + outExcelName);
               context.Response.BodyWriter.WriteAsync(ms.ToArray());*/
        }
        #endregion

        #region 判断是否为数值
        /// <summary>
        /// 判断是否为数值
        /// </summary>
        /// <param name="message">字符</param>
        /// <param name="result">返回结果，true数值；false不是</param>
        /// <returns></returns>
        public static bool isNumeric(String message, out double result)
        {
            Regex rex = new Regex(@"^[-]?d+[.]?d*$");
            result = -1;
            if (rex.IsMatch(message))
            {
                double i;
                if (double.TryParse("message", out i))
                {
                    result = double.Parse(message);
                    return true;
                }
                else
                {
                    return false;
                }
            }
            else
                return false;

        }
        #endregion
        #region 自动设置Excel列宽
        /// <summary>
        /// 自动设置Excel列宽
        /// </summary>
        /// <param name="sheet">Excel表</param>
        private static void AutoSizeColumns(ISheet sheet)
        {
            if (sheet.PhysicalNumberOfRows > 0)
            {
                IRow headerRow = sheet.GetRow(0);

                for (int i = 0, l = headerRow.LastCellNum; i < l; i++)
                {
                    sheet.AutoSizeColumn(i);
                }
            }
        }
        #endregion
        #region 执行sql是否成功:入参查询语句sql,  动态参数SqlParameter；成功返回true,失败返回false
        public static bool ExecSql(string sqlstr)
        {
            SqlConnection Conn = createConn();
            SqlCommand Cmd = new SqlCommand(sqlstr, Conn);
            bool returnBool = false;
            try
            {
                Conn.Open();
                if (Cmd.ExecuteNonQuery() > 0)
                {
                    returnBool = true;
                }
                else
                {
                    returnBool = false;
                }
            }
            catch (System.Exception  )
            {
                //Get_Try_Catch( );
                returnBool = false;
            }
            finally
            {
                Conn.Close();
                Cmd.Dispose();
            }
            return returnBool;
        }
        #endregion
        #region 执行sql是否成功:入参查询语句sql,  动态参数SqlParameter；成功返回true,失败返回false
        /// <summary>
        /// 执行sql是否成功:成功返回true,失败返回false
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <param name="sqlPara">SqlParameter</param>
        /// <returns></returns>    
        public static bool ExecSql(string sql, SqlParameter[] sqlPara)
        {
            sql= sql.ToString().Replace("[", "").Replace("]", "");
            bool returnBool = false;
            SqlConnection Conn = createConn();
            Conn.Open();
            SqlTransaction transaction = Conn.BeginTransaction(); //回滚定义
            SqlCommand cmd = new SqlCommand(sql, Conn);
            if (sqlPara != null)
                cmd.Parameters.AddRange(sqlPara);
            cmd.Transaction = transaction;//添加
            try
            {
                cmd.ExecuteNonQuery();
                transaction.Commit();//提交
                returnBool = true;
            }
            catch (Exception ex)
            {
                Console.Write(ex);
                transaction.Rollback();//回滚
                returnBool = false;
            }
            finally
            {
                cmd.Parameters.Clear();
                Conn.Close();
                cmd.Dispose();
            }
            return returnBool;
        }
        #endregion

        #region 执行sql是否成功:入参查询语句sql,  动态参数SqlParameter；成功返回true,失败返回false
        /// <summary>
        /// 循环执行sql是否成功:成功返回true,失败返回false,在上层执行事务
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <param name="sqlPara">SqlParameter</param>
        /// <returns></returns>    
        public static bool ForExecSql(string sql, SqlParameter[] sqlPara, SqlConnection conn, SqlTransaction st)
        {
            bool returnBool = false;
            SqlConnection Conn = conn;
            //  SqlTransaction transaction = Conn.BeginTransaction(); //回滚定义
            SqlCommand cmd = new SqlCommand(sql, Conn);
            if (sqlPara != null)
                cmd.Parameters.AddRange(sqlPara);
               cmd.Transaction = st;//添加
            try
            {
                cmd.ExecuteNonQuery();
                //  transaction.Commit();//提交
                returnBool = true;
            }
            catch (Exception)
            {
                // transaction.Rollback();//回滚
                returnBool = false;
            }
            finally
            {
                cmd.Parameters.Clear();
                Conn.Close();
                cmd.Dispose();
            }
            return returnBool;
        }
        #endregion

        #region  创建Dictionary
        public static Dictionary<string, object> getAddSuccessDic()
        {
            Dictionary<string, object> dic = new Dictionary<string, object>();
            dic.Add("code", "000");
            dic.Add("msg", "请求成功");
            return dic;
        }
        public static Dictionary<string, object> getAddFailDic()
        {
            Dictionary<string, object> dic = new Dictionary<string, object>();
            dic.Add("code", "001");
            dic.Add("msg", "新增失败");
            return dic;
        }
        #endregion

        #region  批量更新
        /// <summary>
        /// 批量更新
        /// </summary>
        /// <returns></returns>
        public static string BatchUpdate(string sql,SqlParameter[] sqlpar,DataTable dt) 
        {
            SqlConnection conn = null;
            SqlTransaction tr = null;
            try
            {
                conn = MyUtils.GetConn;
                conn.Open();
                tr = conn.BeginTransaction();
                SqlCommand cmd = new SqlCommand();
                cmd.Transaction = tr; 
                SqlDataAdapter sd = new SqlDataAdapter();
                sd.UpdateCommand = new SqlCommand(sql, conn, tr);
                sd.UpdateCommand.Parameters.AddRange(sqlpar);
                //sd.UpdateCommand.Parameters.Add("@RoleID", SqlDbType.NVarChar, 36, "RoleID");
                sd.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
                sd.UpdateBatchSize = 0;
                dt.AcceptChanges();
                foreach (DataRow dr in dt.Rows)
                {
                    dr.SetModified();
                }
                sd.Update(dt);
                tr.Commit();
                return MyUtils.ReturnJsonMsg("1", "success,更新成功");
            }
            catch (Exception  )
            {
                tr.Rollback();
                conn.Close();
                return MyUtils.ReturnJsonMsg("0", "error,更新失败");
            }
            finally
            {
                conn.Close();

            }

        }



        #endregion

    }

